\begin{figure}[H]
\begin{center}
\includegraphics[scale=0.40]{includes/graphics/database_schema_v4.jpg}
\caption{Database schema for the system}
\label{fig:design:database_schema}
\end{center}
\end{figure}

\subsection{Relational design choices}

\subsubsection*{Person table}
The Person table is used for representing persons and has idperson as primary key. A person can either be of type passenger, customer or both. This results in the two attributes "ispassflag" and "iscusflag" are used to indicate which type person is.

\subsubsection*{Booking table}
A booking is related to only one customer and contains the id of the customer that placed the booking. 

\subsubsection*{PassengerBooking table}
PassengerBooking contains the id's of all the passengers that are contained a booking and it uses a combination of idbooking and idpassenger as primary keys. This table allows the same idperson to appear twice in the same booking - both as a customer that placed of the booking and as a passenger.


\subsubsection*{Travel table}
A travel is defined as the start and final stop location of a trip. The attribute, totaltraveltime, is used for storing the total number of hours and minutes that will be used for the entire trip, starting from the first departure airport to the final destination airport, including all the connecting flights. The attribute, numberofstops, is used for storing total the number of connecting flights in a travel.

\subsubsection*{Flight table}
A flight is used to represent a particular route. The same route can be repeated multiple times in a week or month. Attributes timeofdeparture and timeofarrival are used for storing the different dates and times that a particular route is repeated within the same week or month. A physical air plane information is retrieved from the Airplane table via a foreign key, idairplane. All airport information is retrieved from the Airport table via a foreign key, idairport. The attribute traveltime stores the total number of hours that will be used for a particular route. 

\subsubsection*{TravelFlight table}
TravelFlight contains the id's of all flights that are part of a travel. It combines idflight and idtravel as primary key. The same flight can be part of different travels. 



\subsection{Attribute data type in the database}
In order to keep our database simple and consistent, the datatypes have been structured as follows.
\begin{itemize}
\item All IDs is of the type integer as it allows for basic mathematical operations.
\item All strings is of type varchar. Depending of the length of the string, the varchar has a length of 5-10 for short strings and 32 for longer strings.
\item Attributes describing price is of type double.
\item Any type describing date or time e.g. timeOfDeparture and timeOfArrival, the type string is used. Though it is arguable whether it shouldn't have been the type DateTime instead.
\end{itemize}